package br.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import br.com.bean.DisciplinaBean;
import br.com.bean.DisciplinaTurmaBean;
import br.com.bean.TurmaBean;

public class DisciplinaDao {

	public void inserir (Connection conn, DisciplinaBean value) throws Exception{

		String sql = "INSERT INTO disciplina (disciplina) VALUES (?)";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, value.getDisciplina());
		ps.execute();
		ps.close();

	}

	public void alterar (Connection conn, DisciplinaBean value) throws Exception{

		String sql = "UPDATE disciplina SET disciplina=? WHERE  id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, value.getDisciplina());
		ps.execute();
		ps.close();

	}

	public void excluir (Connection conn, DisciplinaBean value) throws Exception {

		String sql = "DELETE FROM disciplina WHERE where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ps.executeUpdate();
	}
	// select para o combo dinamico de disciplina e turma
	public List<DisciplinaTurmaBean> getListaDisc (Connection conn, TurmaBean value) throws Exception{

		String sql = "SELECT dt.id iddistur, d.disciplina disc FROM disciplina_turma dt," +
				" disciplina d WHERE d.id=dt.id_diciplina and dt.id_turma=?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		List<DisciplinaTurmaBean> disciplina = new ArrayList<DisciplinaTurmaBean>();

		DisciplinaTurmaBean a = null;
		while(rs.next()){
			a = new DisciplinaTurmaBean();
			a.setId(rs.getInt("iddistur"));
			a.getId_diciplina().setDisciplina(rs.getString("disc"));
			disciplina.add(a);
		}
		return disciplina;
	}

	// select para o combo simples de disciplina
	public List<DisciplinaBean> getListaDisc (Connection conn) throws Exception{

		String sql = "SELECT * FROM disciplina d WHERE d.id = d.id order by d.disciplina;";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<DisciplinaBean> disciplina = new ArrayList<DisciplinaBean>();

		DisciplinaBean a = null;
		while(rs.next()){
			a = new DisciplinaBean();
			a.setId(rs.getInt("id"));
			a.setDisciplina(rs.getString("disciplina").toString());
			disciplina.add(a);
		}
		return disciplina;
	}
	/*
	public ArrayList<DisciplinaBean> getLista(Connection conn, DisciplinaBean value) throws Exception{

		String sql = "SELECT disciplina FROM cad_disciplina where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		ArrayList<DisciplinaBean> disciplina = new ArrayList<DisciplinaBean>();
		while(rs.next()){
			DisciplinaBean a = new DisciplinaBean();
			a.setDisciplina(rs.getString("disciplina"));
			disciplina.add(a);
		}
		return disciplina;
	}
	public ArrayList<DisciplinaBean> getLista(Connection conn) throws Exception{

		String sql = "SELECT id, disciplina FROM cad_disciplina";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ArrayList<DisciplinaBean> disciplina = new ArrayList<DisciplinaBean>();
		while(rs.next()){
			DisciplinaBean a = new DisciplinaBean();
			a.setId(rs.getInt("id"));
			a.setDisciplina(rs.getString("disciplina"));
			disciplina.add(a);
		}
		return disciplina;
	}*/
}
